01.JDBC 连接、查询和更新

本文我们演示下如何用 JDBC 连接、查询和更新数据库

‍

JDBC 连接

使用 JDBC 时，我们先了解什么是 Connection。Connection 代表一个 JDBC 连接，它相当于 Java 程序到数据库的连接（通常是 TCP 连接）。打开一个 Connection 时，需要准备 URL、用户名和口令，才能成功连接到数据库。

URL 是由数据库厂商指定的格式，例如，MySQL 的 URL 是：

jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2

假设数据库运行在本机 localhost，端口使用标准的 3306，数据库名称是 learnjdbc，那么 URL 如下：

jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8

后面的两个参数表示不使用 SSL 加密，使用 UTF-8 作为字符编码（注意 MySQL 的 UTF-8 是 utf8）。

更多 URL 可以参考下一篇文章，这里先用着。 ‍ 在 Java 中，可以使用字符串来存储连接信息：

// 1. JDBC 连接的 URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
String JDBC_USER = "learn";
String JDBC_PASSWORD = "learnpassword2";

// 2. 注册驱动，告诉 Java 程序我们要使用 MySQL 的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");

// 3.获取连接:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: 访问数据库...
// 关闭连接:
conn.close();

核心代码是 DriverManager 提供的静态方法 getConnection()。

因为 JDBC 连接是一种昂贵的资源，所以使用后要及时释放。使用 try (resource) 来自动释放 JDBC 连接是一个好方法：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    ...
}

在进行查询之前，我们先确保数据库连接正常，完整代码如下：

package chapter2JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCDemo1 {
    public static void main(String[] args) throws SQLException {
        String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
        String JDBC_USER = "learn";
        String JDBC_PASSWORD = "learnpassword2";

        Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        System.out.println(conn);
        conn.close();
    }
}

‍ 如果能正常连接，则可以正常打印 Connection 对象。如果连接失败，会抛出异常，例如我上面的代码中，密码字段写错了：

Exception in thread "main" java.sql.SQLException: Access denied for user 'learn'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at chapter2JDBC.JDBCDemo1.main(JDBCDemo1.java:13)

JDBC 查询

获取到 JDBC 连接后，下一步我们就可以查询数据库了。查询数据库分以下几步：

1. 通过 Connection 提供的 createStatement() 方法创建一个 Statement 对象，用于执行一个查询；
2. 第二步，执行 Statement 对象提供的 executeQuery("SELECT * FROM students") 并传入 SQL 语句，执行查询并获得返回的结果集，使用 ResultSet 来引用这个结果集；
3. 第三步，反复调用 ResultSet 的 next() 方法并读取每一行结果。 ‍ 完整查询代码如下：

package chapter2JDBC;

import java.sql.*;

public class JDBCDemo2Select {
    public static void main(String[] args) throws SQLException {
        String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
        String JDBC_USER = "learn";
        String JDBC_PASSWORD = "learnpassword";

        Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery("select id, grade, name, gender from students where gender = 1");
        while (rs.next()){
            long id = rs.getLong(1); //注意：索引从 1 开始
            long grade = rs.getLong(2);
            String name = rs.getString(3);
            int gender = rs.getInt(4);
            System.out.printf("id: %s, grade: %s, name: %s, gender:%s, \n", id, grade,name, gender);
        }
        rs.close();
        statement.close();
        conn.close();
    }
}

‍ 执行结果：

id: 1, grade: 1, name: 小明, gender:1 
id: 2, grade: 1, name: 小红, gender:1 
id: 5, grade: 2, name: 小牛, gender:1 
id: 6, grade: 2, name: 小兵, gender:1 
id: 9, grade: 3, name: 小青, gender:1 
id: 10, grade: 3, name: 小王, gender:1

注意要点：

• Statment 和 ResultSet 都是需要关闭的资源，可以嵌套使用 try (resource) 确保及时关闭。

• 关闭和打开的顺序：
  打开时：Connection -> Statement -> ResultSet
  关闭时：ResultSet-> Statement -> Connection.

  建议使用 try-catch 块执行关闭，以防前面关闭意外出错了，导致后面的代码就不执行了，进而导致后面的关闭失败。

• rs.next() 用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行（一开始获得 ResultSet 时当前行不是第一行）；

• ResultSet 获取列时，索引从 1 开始而不是 0；

• 必须根据 SELECT 的列的对应位置来调用 getLong(1)，getString(2) 这些方法，否则对应位置的数据类型不对，将报错。

• 也可以使用 getString(列名); 的方式来获取数据，这样不易出错。

• 注意到 JDBC 查询的返回值总是 ResultSet，即使我们写这样的聚合查询 SELECT SUM(score) FROM ...，也需要按结果集读取。

数据类型

有的童鞋可能注意到了，使用 JDBC 的时候，我们需要在 Java 数据类型和 SQL 数据类型之间进行转换。JDBC 在 java.sql.Types 定义了一组常量来表示如何映射 SQL 数据类型，但是平时我们使用的类型通常也就以下几种：

SQL 数据类型	Java 数据类型
BIT, BOOL	boolean
INTEGER	int
BIGINT	long
REAL	float
FLOAT, DOUBLE	double
CHAR, VARCHAR	String
DECIMAL	BigDecimal
DATE	java.sql.Date, LocalDate
TIME	java.sql.Time, LocalTime

注意：只有最新的 JDBC 驱动才支持 LocalDate 和 LocalTime。 ‍

SQL 注入攻击

使用 Statement 拼字符串非常容易引发 SQL 注入的问题，这是因为 SQL 参数往往是从方法参数传入的。

那么什么是 SQL 注入呢？我们来看一个例子：假设用户登录的验证方法如下：

User login(String name, String pass) {
    ...
    stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
    ...
}

其中，参数 name 和 pass 通常都是 Web 页面输入后由程序接收到的。 ‍ 如果用户的输入是程序期待的值，就可以拼出正确的 SQL。例如：name = "bob"，pass = "1234"：

SELECT * FROM user WHERE login='bob' AND pass='1234'

但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的 SQL，这个 SQL 也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''

这个 SQL 语句执行的时候，根本不用判断口令是否正确，这样一来，登录就形同虚设。 ‍

PreparedStatement

要避免 SQL 注入攻击，一个办法是针对所有字符串参数进行转义，但是转义很麻烦，而且需要在任何使用 SQL 的地方增加转义代码。

还有一个办法就是使用 PreparedStatement。使用 PreparedStatement 可以 完全避免 SQL 注入的问题，因为 PreparedStatement 始终使用 ? 作为占位符，并且把数据连同 SQL 本身传给数据库，这样可以保证每次传给数据库的 SQL 语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录 SQL 如果用 PreparedStatement 可以改写如下：

User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

所以，PreparedStatement 比 Statement 更安全，而且更快。 ‍ 使用 Java 对数据库进行操作时，必须使用 PreparedStatement，严禁任何通过参数拼字符串的代码！

我们把上面使用 Statement 的代码改为使用 PreparedStatement：

package chapter2JDBC;

import java.sql.*;

public class JDBCDemo3PreparedStatement {
    public static void main(String[] args) throws SQLException {
        String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
        String JDBC_USER = "learn";
        String JDBC_PASSWORD = "learnpassword";

        Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        PreparedStatement statement = conn.prepareStatement("select id, grade, name, gender from students where gender = ? and grade = ?");
        statement.setObject(1, "M"); //注意：索引从 1 开始
        statement.setObject(2, 3); //注意：索引从 1 开始

        ResultSet rs = statement.executeQuery();
        while (rs.next()){
            long id = rs.getLong("id");
            long grade = rs.getLong("grade");
            String name = rs.getString("name");
            int gender = rs.getInt("gender");
            System.out.printf("id: %s, grade: %s, name: %s, gender:%s \n", id, grade,name, gender);
        }
        rs.close();
        statement.close();
        conn.close();
    }
}

‍ 运行结果：

id: 11, grade: 3, name: 小林, gender:0 
id: 12, grade: 3, name: 小贝, gender:0

使用 PreparedStatement 和 Statement 稍有不同，必须首先调用 setObject() 设置每个占位符 ? 的值，最后获取的仍然是 ResultSet 对象。

模糊查询写法

有时候我们需要用到模糊查询，也就是 like。我们第一反应可能是这样写：

String sql = "select * from students where name like %小%";

这是错误的写法，因为%是特殊字符，正确写法：

String sql = "select * from students where name like '%小%'";
String sql = "select * from students where name like '小%'";
String sql = "select * from students where name like '%小'";

// 或者用拼接
String sql = "select * from [dbo].[sal] where empno  like '%"+ "小" + "'";
String sql = "select * from students where name like '%"+ "小" + "'";
String sql = "select * from students where name like '"+ "小" + "%'"

如果使用 PreparedStatement：

PreparedStatement statement2 = conn.prepareStatement("select * from students where name like ?");
statement2.setObject(1, "%小%");

存储过程

有时候我们会用到存储过程，调用存储过程语法如下：

CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, "dylan thomas");
proc.setInt(2, 69);
cs.execute();

‍

总结

JDBC 接口的 Connection 代表一个 JDBC 连接；

使用 JDBC 查询的步骤如下：

1. 确定 JDBC 连接字符串
2. 使用 DriverManager 类创建 Connection 对象
3. 创建 Statement 对象
4. 配置要执行的 SQL，并使用 executeQuery 方法来执行 SQL
5. 反复调用 ResultSet 的 next() 方法并读取每一行结果
6. 注意关闭资源 ‍ 每个对象的作用：

• DriverManager：驱动管理对象
• Connection：数据库连接对象
• Statement、PreparedStatement：执行 sql 的对象
• ResultSet：结果集对象，封装查询结果

注意：

• 使用 JDBC 查询时，总是使用 PreparedStatement 进行查询而不是 Statement；
• 查询结果总是 ResultSet，即使使用聚合查询也不例外。

完整代码已上传至 Gitee 和 GitHub：

Gitee：src/chapter2JDBC · 小林/LearnJavaEE - 码云 - 开源中国

GitHub：LearnJavaEE/src/chapter2JDBC at master · Peter-JXL/LearnJavaEE ‍